package com.meiyuetao.myt.c2c.web.action;

import java.math.BigDecimal;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import lab.s2jh.core.annotation.MetaData;
import lab.s2jh.core.pagination.PropertyFilter;
import lab.s2jh.core.service.BaseService;
import lab.s2jh.web.action.BaseController;

import org.apache.struts2.rest.HttpHeaders;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;

import com.meiyuetao.myt.c2c.entity.C2cShopAccount;
import com.meiyuetao.myt.c2c.service.C2cShopAccountService;
import com.meiyuetao.myt.c2c.service.C2cShopInfoService;

@MetaData("店铺账户管理")
public class C2cShopAccountController extends BaseController<C2cShopAccount, Long> {

    @Autowired
    private C2cShopInfoService c2cShopInfoService;

    @Autowired
    private C2cShopAccountService c2cShopAccountService;

    @Override
    protected BaseService<C2cShopAccount, Long> getEntityService() {
        return c2cShopAccountService;
    }

    @Override
    protected void checkEntityAclPermission(C2cShopAccount entity) {

    }

    @Autowired
    private JdbcTemplate jdbcTemplate;

    public HttpHeaders findByShop() {
        Pageable pageable = PropertyFilter.buildPageableFromHttpRequest(getRequest());
        int pageSize = pageable.getPageSize();
        int pageNum = pageable.getPageNumber();
        int startNum = pageNum * pageSize + 1;
        int endNum = (pageNum + 1) * pageSize + 1;
        String shopName = this.getParameter("shopName", "%%");
        if (!"%%".equals(shopName)) {
            shopName = "%" + shopName + "%";
        }
        StringBuilder sql = new StringBuilder();
        sql.append("WITH query AS ( SELECT inner_query.*, ROW_NUMBER () OVER (ORDER BY CURRENT_TIMESTAMP) AS row_nr FROM ( SELECT TOP (:endNum) ");
        sql.append("s.shop_name AS shopName, ");
        sql.append("SUM(CASE WHEN f.account_type_code= 'A00' THEN f.amount ELSE 0 END )AS total, ");
        sql.append("SUM(CASE WHEN f.account_type_code= 'B00' THEN f.amount ELSE 0 END )AS overage, ");
        sql.append("SUM(CASE WHEN f.account_type_code= 'B01' THEN f.amount ELSE 0 END )AS subsidy, ");
        sql.append("SUM(CASE WHEN f.account_type_code= 'B02' THEN f.amount ELSE 0 END )AS bail ");
        sql.append("FROM c2c_shop_info s ");
        sql.append("LEFT JOIN  c2c_finance_account f ");
        sql.append("ON s.sid = f.shop_sid ");
        sql.append("WHERE shop_name LIKE :shopName ");
        sql.append("GROUP BY s.shop_name ");
        sql.append(") inner_query ) SELECT shopName,total,overage,subsidy,bail FROM query ");
        sql.append("WHERE row_nr >= :startNum AND row_nr < :endNum ");

        NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);
        MapSqlParameterSource args = new MapSqlParameterSource();
        args.addValue("shopName", shopName);
        args.addValue("startNum", startNum);
        args.addValue("endNum", endNum);

        List<C2cShopAccount> datas = namedParameterJdbcTemplate.query(sql.toString(), args, new RowMapper<C2cShopAccount>() {
            public C2cShopAccount mapRow(final ResultSet rs, final int rowNum) throws SQLException {
                C2cShopAccount c2cShopAccount = new C2cShopAccount();
                String shopName = rs.getString("shopName");
                BigDecimal total = rs.getBigDecimal("total");
                BigDecimal overage = rs.getBigDecimal("overage");
                BigDecimal subsidy = rs.getBigDecimal("subsidy");
                BigDecimal bail = rs.getBigDecimal("bail");
                c2cShopAccount.setShopName(shopName);
                c2cShopAccount.setTotal(total);
                c2cShopAccount.setOverage(overage);
                c2cShopAccount.setSubsidy(subsidy);
                c2cShopAccount.setBail(bail);
                return c2cShopAccount;
            }
        });
        StringBuilder sqlCount = new StringBuilder();
        sqlCount.append("SELECT COUNT(s.sid) as rowSize FROM c2c_shop_info s");
        List<Integer> count = namedParameterJdbcTemplate.query(sqlCount.toString(), args, new RowMapper<Integer>() {
            public Integer mapRow(final ResultSet rs, final int rowNum) throws SQLException {
                String rowSize = rs.getString("rowSize");
                return Integer.valueOf(rowSize);
            }
        });
        setModel(new PageImpl<C2cShopAccount>(datas, pageable, count.get(0)));
        return buildDefaultHttpHeaders();
    }

    @Override
    @MetaData("创建")
    public HttpHeaders doCreate() {
        return super.doCreate();
    }

    @Override
    @MetaData("更新")
    public HttpHeaders doUpdate() {
        return super.doUpdate();
    }

    @Override
    @MetaData("保存")
    public HttpHeaders doSave() {
        return super.doSave();
    }

    @Override
    @MetaData("删除")
    public HttpHeaders doDelete() {
        return super.doDelete();
    }

    @Override
    @MetaData("查询")
    public HttpHeaders findByPage() {
        return super.findByPage();
    }
}